3.07. Транзакции и блокировки

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Транзакции и блокировки

О блокировках

Блокировки в базах данных применяются для обеспечения согласованности данных при параллельном доступе нескольких транзакций. Уровень блокировки определяет объём ресурса, который изолируется от конкурентных изменений. Основные уровни блокировок — блокировка таблицы, страницы и записи (строки). Каждый из них представляет собой компромисс между производительностью, параллелизмом и накладными расходами.

1. Блокировка записи (Row-level locking)

На этом уровне блокируется отдельная строка таблицы. Это наиболее гранулярный тип блокировки, обеспечивающий высокий уровень параллелизма.

Пример использования:

UPDATE employees SET salary = salary * 1.1 WHERE id = 101;

СУБД заблокирует только строку с id = 101, остальные строки остаются доступны для изменений.

Поддержка: InnoDB (MySQL), PostgreSQL, Oracle, SQL Server.

2. Блокировка страницы (Page-level locking)

Страница — это минимальная единица хранения данных на диске (обычно 8 КБ или 16 КБ). При блокировке страницы все строки, находящиеся на этой странице, становятся недоступными для модификации конкурентными транзакциями.

Реже встречается в современных СУБД. Например, некоторые режимы работы в SQL Server или устаревшие движки MySQL (MyISAM не поддерживает блокировку строк).

3. Блокировка таблицы (Table-level locking)

При этом виде блокировки вся таблица целиком становится недоступной для изменений (или чтения, в зависимости от типа блокировки) для других транзакций.

Пример:

LOCK TABLES employees WRITE;
UPDATE employees SET salary = salary + 1000;
UNLOCK TABLES;

Также неявная блокировка таблицы может происходить при выполнении DDL-операций (ALTER TABLE) или при использовании движков, не поддерживающих блокировку строк (например, MyISAM в MySQL).

Современные системы стремятся использовать блокировку строк как стандарт для транзакционных систем, так как она обеспечивает наилучший баланс между масштабируемостью и целостностью данных. Блокировка таблицы допустима в случаях массовой загрузки или обслуживания, но не рекомендуется в активных OLTP-средах.

---

О транзакциях

Транзакции – это фундаментальная концепция баз данных, которая обеспечивает надёжную обработку данных. ACID – это набор свойств, гарантирующих корректность операций даже при сбоях.

★ Транзакция – это последовательность SQL-операций, которая выполняется как единое целое. Либо все операции выполняются успешно, либо одна из них не применяется.

Особенность в том, что БД – набор данных, и кода мы поработаем, внесём изменения, они не будут видны другим, пока мы не нажмём «Сохранить» (в SQL это COMMIT). Если мы хотим отменить изменения – ROLLBACK, и правки исчезнут. Это нужно, чтобы не было хаоса, пока два и более человека меняют одни данные одновременно, или если произойдёт сбой во время важной операции.

Большинство СУБД и инструментов по умолчанию используют автокоммит – каждый наш SQL-запрос (INSERT, UPDATE, DELETE) автоматически становится отдельной транзакцией и сразу применяется (COMMIT). Пример:

UPDATE users SET name = 'Анна' WHERE id = 1;  -- СУБД сама делает COMMIT

В GUI СУБД (Oracle, DBeaver, DataGrip) есть кнопки, к примеру, Commit или Rollback – ручное управление транзакциями. Пока мы не нажмём на Commit, другие пользователи не увидят наших изменений. Когда мы пишем сторонний код, допустим, на C#, то по умолчанию автокоммит тоже включен.

Автокоммит не подходит для связанных операций, где важно, чтобы:

• либо выполнились все шаги, либо ни один;
• другие пользователи не видели «промежуточных» данных.

Примеры:

Без транзакции:

-- Шаг 1: Списать 100₽ счёта А
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Шаг 2: Зачислить 100₽ на счёт Б
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

С транзакцией:

BEGIN TRANSACTION;  -- Отключаем автокоммит

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- Подтверждаем оба изменения
-- или ROLLBACK; в случае ошибки

Таким образом, транзакции - «безопасный режим» для важных операций. Если вам, в целом, нужно просто обучение на SQL, вы и не столкнётесь с транзакциями, когда просто надо сделать, к примеру, обычные SELECT/INSERT. Но когда дело касается сложных вычислений и огромных операций, то, чтобы избежать «частичных изменений», конфликтов и промежуточных правок, нужно выполнять всё целиком – тогда и нужны транзакции.

---

ACID

★ ACID – это расшифрока из четырёх ключевых свойств:

Свойство	Описание
Atomicity (Атомарность)	Все операции в транзакции выполняются как единое целое. Если одна операция отменена, отменяются все.
Consistecy (Согласованность)	Данные переходят из одного корректного состояния в другое. Баланс счетов не может быть отрицательным, например.
Isolation (Изолированность)	Параллельные транзакции не мешают друг другу. К примеру, два перевода одних денег не создают конфликт.
Durability (Долговечность)	Результаты завершённых транзакций сохраняются даже при сбоях. Пример – после подтверждения перевод не пропадёт при отключении питания.

Пример синтаксиса:

START TRANSACTION;  -- Начало транзакции

-- SQL-операции
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- Завершение транзакции
COMMIT;  -- Подтверждение изменений
-- или
ROLLBACK;  -- Отмена всех изменений в транзакции

Разные СУБД поддерживают различные уровни изоляции (от самого слабого к самому строгому:

• Read Uncommited – можно читать «грязные» (незафиксированные) данные;
• Read Commited – читаются только подтверждённые данные (стандарт PostgreSQL);
• Repeatable Read – гарантирует, что повторное чтение даст те же данные (стандарт MySQL);
• Serializable – полная изоляция, как последовательное выполнение.

MySQL/InnoDB поддерживают все уровни ACID;

PostgreSQL поддерживает все уровни ACID;

SQL Server имеет расширенные возможности;

SQLite – ACID поддерживается, но весь файл БД блокируется при записи.